package com.example.psychologicalappointmentsystem.JpaRepository;

import com.example.psychologicalappointmentsystem.Entity.ChatMessageDO;
import com.example.psychologicalappointmentsystem.Entity.OrderDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * ClassName ChatMessageRepository
 * Description TODO
 * Date 2025/2/14 16:42
 * Version 1.0
 */
@Repository
public interface ChatMessageRepository extends JpaRepository<ChatMessageDO, Long> {

    //发送的和接受的消息都会返回
    @Query(value = "select * from chat_message where (sender_id = :userId or receiver_id =:userId) and (sender_id = :receiverId or receiver_id = :receiverId) and TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ORDER BY TIMESTAMP desc",nativeQuery = true)
    List<ChatMessageDO> findOrderslast7Day(@Param("userId") Long userId,
                                           @Param("receiverId") Long receiverId);

    @Query(value = "select * from chat_message where receiver_id = :userId and TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ORDER BY TIMESTAMP desc",nativeQuery = true)
    List<ChatMessageDO> findOrderslast7DayReceiver(@Param("userId") Long userId);

    //用户的历史
    @Query(value = "SELECT receiver_id FROM chat_message where sender_id = :userId GROUP BY receiver_id ORDER BY MAX(timestamp) DESC",nativeQuery = true)
    List<Long> findOrdersSession(@Param("userId") Long userId);

    //咨询师的历史
    @Query(value = "SELECT sender_id FROM chat_message where receiver_id = :userId GROUP BY sender_id ORDER BY MAX(timestamp) DESC",nativeQuery = true)
    List<Long> findOrdersConstentSession(@Param("userId") Long userId);

    //返回总未读消息
    @Query(value = "SELECT count(*) FROM chat_message where receiver_id = :userId and status = 'SENT' and TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)",nativeQuery = true)
    Integer findOrdersUnread(@Param("userId") Long userId);

    //返回用户作为被接受方，的未读消息
    @Query(value = "SELECT sender_id,count(*) as count FROM chat_message where receiver_id = :userId and status = 'SENT' and TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY sender_id",nativeQuery = true)
    List<Object[]> findOrdersGroupUnread(@Param("userId") Long userId);



}
